﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.IO;
using LogManager;
using System.Data.Common;
namespace DBManager
{

    //保存数据库操作结果
    public class DBResult
    {
        public bool bSucceedFlag = true;
        public DataTable dtTable = null;
        public Object obj = null;
        public string strMessage = "";
        public int iRowCount = 0;
    }

    public class DBReader
    {
        public const string LogType = "DBManager";//日志类别（记录日志时使用。例： Logger.Write(LogType,msg）
        private static Dictionary<string, Database> m_DBArray = new Dictionary<string, Database>();
        private static string key = "judypass";
        private static string DB_TYPE_ORACLE = "ORACLE";
        private static string DB_TYPE_SQLSERVER = "SQLSERVER";
        private static string DB_TYPE_MYSQL = "MYSQL";
        private static string DB_TYPE_OLEDB = "OLEDB";
        private static Dictionary<string, string> m_DBTypeList = new Dictionary<string, string>();
        private static System.Text.RegularExpressions.Regex _providerRex = new System.Text.RegularExpressions.Regex("Provider[^;]+;|provider[^;]+;|PROVIDER[^;]+;");

        #region//记录日志
        private static void WritLog(DBResult rs)
        {
            if ((rs != null) && (rs.bSucceedFlag == false) && (string.IsNullOrEmpty(rs.strMessage) == false))
            {
                Logger.Write(LogType, rs.strMessage);
            }
        }
        private static void WritLog(string strErrorMsg)
        {
            Logger.Write(LogType, strErrorMsg);
        }
        #endregion

        #region//获取连接字符串
        public static string GetConnectString(string strDBName)
        {
            string strConnstring = "";
            Database DBTmp = null;
            try
            {
                DBTmp = DatabaseFactory.CreateDatabase(strDBName);

                strConnstring = DBTmp.ConnectionString;
                try
                {
                    strConnstring = DESDecrypt(strConnstring);
                }
                catch
                {
                }
            }
            catch
            {

            }
            return strConnstring;
        }
        #endregion

        #region//解密连接字符串
        private static string DESDecrypt(string decryptString)
        {
            try
            {
                using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())
                {

                    byte[] inputByteArray = new byte[decryptString.Length / 2];
                    //反轉 
                    for (int x = 0; x < decryptString.Length / 2; x++)
                    {
                        int i = (Convert.ToInt32(decryptString.Substring(x * 2, 2), 16));
                        inputByteArray[x] = (byte)i;
                    }
                    //設定加密金鑰(轉為Byte) 
                    des.Key = ASCIIEncoding.ASCII.GetBytes(key);
                    //設定初始化向量(轉為Byte) 
                    des.IV = ASCIIEncoding.ASCII.GetBytes(key);
                    using (MemoryStream ms = new MemoryStream())
                    {
                        using (CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write))
                        {
                            //例外處理 
                            try
                            {
                                cs.Write(inputByteArray, 0, inputByteArray.Length);
                                cs.FlushFinalBlock();
                                //輸出資料 
                                return System.Text.Encoding.Default.GetString(ms.ToArray());
                            }
                            catch (CryptographicException)
                            {
                                //若金鑰或向量錯誤，傳回N/A 
                                return decryptString;
                            }
                        }
                    }
                }
            }
            catch
            {
                return decryptString;
            }
        }
        #endregion

        #region//创建数据库对象
        private static Database CreateDB(string strDBName)
        {
            if (string.IsNullOrEmpty(strDBName) == true)
                return null;
            if (m_DBArray.ContainsKey(strDBName) == true)
                return m_DBArray[strDBName];
            Database DBLocal = null, DBTmp = null;
            OracleDatabase DBOracle = null;
            MySqlDatabase DBMySQL = null;
            OledbDatabase DBOledb = null;

            string strErrorMessage = "";
            try
            {
                DBTmp = DatabaseFactory.CreateDatabase(strDBName);
                try
                {
                    string strConnstring = DBTmp.ConnectionString;
                    try
                    {
                        strConnstring = DESDecrypt(strConnstring);
                    }
                    catch
                    {
                    }
                    string noProviderStr = strConnstring;
                    if (_providerRex.Match(strConnstring).Value.Length > 0)
                    {
                        noProviderStr = strConnstring.Replace(_providerRex.Match(strConnstring).Value, "");
                    }
                    if (DBTmp.DbProviderFactory is System.Data.OleDb.OleDbFactory)
                    {
                        DBOledb = new OledbDatabase(strConnstring);
                    }
                    else if (DBTmp.DbProviderFactory is System.Data.OracleClient.OracleClientFactory)
                    {
                        DBOracle = new OracleDatabase(noProviderStr);
                    }
                    else if (DBTmp.DbProviderFactory is MySql.Data.MySqlClient.MySqlClientFactory)
                    {
                        DBMySQL = new MySqlDatabase(noProviderStr);
                    }
                    else
                        DBLocal = new SqlDatabase(noProviderStr);

                    //int pos1 = strConnstring.ToUpper().IndexOf("PROVIDER");
                    //if (pos1 >= 0)
                    //{
                    //    if ((strConnstring.ToUpper().IndexOf("MSDAORA") >= 0) ||
                    //        (strConnstring.ToUpper().IndexOf("ORAOLEDB") >= 0) ||
                    //        (strConnstring.ToUpper().IndexOf("ORACLE") >= 0))
                    //    {
                    //        int pos2 = strConnstring.IndexOf(";", pos1);
                    //        if (pos2 > pos1)
                    //            strConnstring = strConnstring.Substring(pos2 + 1);
                    //        else
                    //            strConnstring = strConnstring.Substring(0, pos1);
                    //        DBOracle = new OracleDatabase(strConnstring);
                    //    }
                    //    else
                    //    {
                    //        DBOracle = null;

                    //        if (strConnstring.ToUpper().IndexOf("MYSQL") >= 0)
                    //        {
                    //            int pos2 = strConnstring.IndexOf(";", pos1);
                    //            if (pos2 > pos1)
                    //                strConnstring = strConnstring.Substring(pos2 + 1);
                    //            else
                    //                strConnstring = strConnstring.Substring(0, pos1);
                    //            DBMySQL = new MySqlDatabase(strConnstring);
                    //        }
                    //        else
                    //            DBMySQL = null;
                    //    }
                    //}
                }
                catch (Exception ex)
                {
                    strErrorMessage = "创建数据库连接失败:" + ex.Message;
                }
            }
            catch (Exception ex)
            {
                strErrorMessage = "创建数据库连接失败:" + ex.Message;
            }
            if (string.IsNullOrEmpty(strErrorMessage) == false)
                WritLog(strErrorMessage);
            if (DBOledb != null)
            {
                //LogManager.Logger.Info("数据库对象创建成功（OLEDB）");
                m_DBArray.Add(strDBName, DBOledb);
                m_DBTypeList.Add(strDBName, DB_TYPE_OLEDB);
                return DBOledb;
            }
            else if (DBOracle != null)
            {
                //LogManager.Logger.Info("数据库对象创建成功（Oracle）");
                m_DBArray.Add(strDBName, DBOracle);
                m_DBTypeList.Add(strDBName, DB_TYPE_ORACLE);
                return DBOracle;
            }
            else if (DBMySQL != null)
            {
                //LogManager.Logger.Info("数据库对象创建成功（MySQL）");
                m_DBArray.Add(strDBName, DBMySQL);
                m_DBTypeList.Add(strDBName, DB_TYPE_MYSQL);
                return DBMySQL;
            }
            else if (DBLocal != null)
            {
                //LogManager.Logger.Info("数据库对象创建成功（默认SQLServer）");
                m_DBArray.Add(strDBName, DBLocal);
                m_DBTypeList.Add(strDBName, DB_TYPE_SQLSERVER);
                return DBLocal;
            }
            else
                return null;
        }

        #endregion

        #region//判断是否为oracle数据库
        public static bool IsOracleDatabase(string strDBName)
        {
            string strDBType = "";
            if (m_DBTypeList.ContainsKey(strDBName))
                strDBType = m_DBTypeList[strDBName];
            if (strDBType == DB_TYPE_ORACLE)
                return true;
            else
                return false;
        }
        #endregion

        #region//获取数据表
        public static DBResult GetDataTable(string strDBName, string strSQL)
        {
            return GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL));
        }

        public static DBResult GetDataTable(string strDBName, string strSQL, object p1)
        {
            return GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1));
        }

        public static DBResult GetDataTable(string strDBName, string strSQL, object p1, object p2)
        {
            return GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2));
        }

        public static DBResult GetDataTable(string strDBName, string strSQL, object p1, object p2, object p3)
        {
            return GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3));
        }

        public static DBResult GetDataTable(string strDBName, string strSQL, object p1, object p2, object p3, object p4)
        {
            return GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4));
        }

        public static DBResult GetDataTable(string strDBName, string strSQL, object p1, object p2, object p3, object p4, object p5)
        {
            return GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4, p5));
        }

        public static DBResult GetDataTable(string strDBName, string strSQL, object[] px)
        {
            return GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, px));
        }

        private static DBResult GetDataTable2(string strDBName, string strSQL)
        {
            int iCommandTimeout = 1800;
            try
            {
                string strCommandTimeout = ConfigurationManager.AppSettings["COMMAND_TIMEOUT"]?.ToString() ?? "1800";
                iCommandTimeout = Convert.ToInt32(strCommandTimeout);
            }
            catch (Exception ex)
            {
                iCommandTimeout = 1800;
            }

            DBResult result = new DBResult();
            DataSet ds = null;
            Database db = null;
            try
            {
                db = CreateDB(strDBName);
                if (db == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                else
                {
                    result.bSucceedFlag = true;
                }
                if (result.bSucceedFlag == true)
                {
                    DbConnection conn = db.CreateConnection();
                    conn.Open();
                    DbCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = iCommandTimeout;
                    cmd.CommandText = strSQL;

                    ds = db.ExecuteDataSet(cmd);
                    conn.Close();
                    // ds = db.ExecuteDataSet(CommandType.Text, strSQL);
                }
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行查询语句失败，原因：" + ex.Message + " (SQL：" + strSQL + ")";
                ds = null;
            }
            if ((ds == null) || (ds.Tables.Count == 0))
            {
                result.iRowCount = 0;
            }
            else
            {
                result.dtTable = ds.Tables[0];
                result.iRowCount = ds.Tables[0].Rows.Count;
            }
            WritLog(result);
            return result;
        }
        #endregion

        #region//获取大字段
        public static DBResult GetDataTableForLargeData(string strDBName, string strSQL)
        {
            DBResult result = new DBResult();
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            //OracleDatabase oraDB = db as OracleDatabase;
            //if (oraDB == null)
            return GetDataTable(strDBName, strSQL);
            //else
            //    return GetDataTableForBlob(strDBName, strSQL, null);
        }

        private static DBResult GetDataTableForBlob(string strDBName, string strSQL, object[] px)
        {
            string sql = SQLHelper.Extend(strDBName, strSQL, px);
            DBResult result = new DBResult();
            DataSet ds = null;
            try
            {
                OracleDatabase dbOracle = CreateDB(strDBName) as OracleDatabase;
                if (dbOracle != null)
                {
                    ds = dbOracle.ExecuteDataSet(CommandType.Text, sql);
                    result.bSucceedFlag = true;
                }
                else
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                }
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "oracle数据获取失败:" + ex.Message;
                ds = null;
            }
            if ((ds == null) || (ds.Tables.Count == 0))
            {
                result.iRowCount = 0;
            }
            else
            {
                result.dtTable = ds.Tables[0];
                result.iRowCount = ds.Tables[0].Rows.Count;
            }
            WritLog(result);
            return result;
        }
        #endregion

        #region//获取数据项
        public static DBResult GetDataValue(string strDBName, string strSQL)
        {
            return GetDataValue2(strDBName, SQLHelper.Extend(strDBName, strSQL));
        }

        public static DBResult GetDataValue(string strDBName, string strSQL, object p1)
        {
            return GetDataValue2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1));
        }

        public static DBResult GetDataValue(string strDBName, string strSQL, object p1, object p2)
        {
            return GetDataValue2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2));
        }

        public static DBResult GetDataValue(string strDBName, string strSQL, object p1, object p2, object p3)
        {
            return GetDataValue2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3));
        }

        public static DBResult GetDataValue(string strDBName, string strSQL, object p1, object p2, object p3, object p4)
        {
            return GetDataValue2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4));
        }

        public static DBResult GetDataValue(string strDBName, string strSQL, object p1, object p2, object p3, object p4, object p5)
        {
            return GetDataValue2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4, p5));
        }

        public static DBResult GetDataValue(string strDBName, string strSQL, object[] px)
        {
            return GetDataValue2(strDBName, SQLHelper.Extend(strDBName, strSQL, px));
        }

        private static DBResult GetDataValue2(string strDBName, string strSQL)
        {
            DBResult result = new DBResult();
            Database db = null;
            object obj = null;
            try
            {
                db = CreateDB(strDBName);
                if (db == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                obj = db.ExecuteScalar(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行查询语句失败，原因：" + ex.Message + " (SQL：" + strSQL + ")";
                obj = null;
            }
            if (obj == null)
            {
                result.obj = null;
                result.iRowCount = 0;
            }
            else
            {
                result.obj = obj;
                result.iRowCount = 0;
            }
            WritLog(result);
            return result;
        }
        #endregion

        #region//获取第一行第一个值
        public static object GetTopLeft(string strDBName, string strSQL)
        {
            return GetTopLeft2(strDBName, SQLHelper.Extend(strDBName, strSQL));
        }
        public static object GetTopLeft(string strDBName, string strSQL, object p1)
        {
            return GetTopLeft2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1));
        }
        public static object GetTopLeft(string strDBName, string strSQL, object p1, object p2)
        {
            return GetTopLeft2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2));
        }
        public static object GetTopLeft(string strDBName, string strSQL, object p1, object p2, object p3)
        {
            return GetTopLeft2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3));
        }
        public static object GetTopLeft(string strDBName, string strSQL, object p1, object p2, object p3, object p4)
        {
            return GetTopLeft2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4));
        }
        public static object GetTopLeft(string strDBName, string strSQL, object p1, object p2, object p3, object p4, object p5)
        {
            return GetTopLeft2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4, p5));
        }
        public static object GetTopLeft(string strDBName, string strSQL, object[] p)
        {
            return GetTopLeft2(strDBName, SQLHelper.Extend(strDBName, strSQL, p));
        }

        private static object GetTopLeft2(string strDBName, string strSQL)
        {
            DBResult result = GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL));
            if (result.bSucceedFlag == true)
            {
                if ((result.dtTable != null) && (result.dtTable.Rows.Count > 0))
                    return result.dtTable.Rows[0][0];
                else if (result.obj != null)
                    return result.obj;
                else
                    return null;
            }
            else
                return null;
        }
        #endregion

        #region//获取第一行的数据
        public static object GetTopRow(string strDBName, string strSQL)
        {
            return GetTopRow2(strDBName, SQLHelper.Extend(strDBName, strSQL));
        }
        public static object GetTopRow(string strDBName, string strSQL, object p1)
        {
            return GetTopRow2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1));
        }
        public static object GetTopRow(string strDBName, string strSQL, object p1, object p2)
        {
            return GetTopRow2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2));
        }
        public static object GetTopRow(string strDBName, string strSQL, object p1, object p2, object p3)
        {
            return GetTopRow2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3));
        }
        public static object GetTopRow(string strDBName, string strSQL, object p1, object p2, object p3, object p4)
        {
            return GetTopRow2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4));
        }
        public static object GetTopRow(string strDBName, string strSQL, object p1, object p2, object p3, object p4, object p5)
        {
            return GetTopRow2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4, p5));
        }
        public static object GetTopRow(string strDBName, string strSQL, object[] p)
        {
            return GetTopRow2(strDBName, SQLHelper.Extend(strDBName, strSQL, p));
        }

        private static DataRow GetTopRow2(string strDBName, string strSQL)
        {
            DBResult result = GetDataTable2(strDBName, SQLHelper.Extend(strDBName, strSQL));
            if (result.bSucceedFlag == true)
            {
                if ((result.dtTable != null) && (result.dtTable.Rows.Count > 0))
                    return result.dtTable.Rows[0];
                else
                    return null;
            }
            else
                return null;
        }
        #endregion
    }

}
